package com.hk.commons;

import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;

/**
 * 
 * Excel 工具类
 * @author jeff
 * 
 */
public class ExcelHelper {
	private XSSFWorkbook wb = null;

	private XSSFSheet sheet = null;

	private List body = null;
	
	private String[] head = null;

	private XSSFCellStyle cellstyle = null;

	/**
	 * 
	 * @param body
	 * @param fileFullName, 文件的全路径名
	 * @param body 传入的参数，为List 类型的链表
	 */
	public ExcelHelper(List<?> body, String headStr) {
		this.wb = new XSSFWorkbook();
		
		//定义Sheet 的名字，默认为sheet1
		this.sheet = wb.createSheet("Sheet1");
		this.setBody(body);
		this.head = headStr.split(",");

		this.cellstyle = wb.createCellStyle();

		createFile();
//		setColWidth();
	}

	private void createFile() {
		
		// 设置表格的正文
		createNormalBody();

	}


	public void createNormalBody() {
		
		//创建表头
		cteateHead();
		
		// 用i 控制行数
		for (int row = 0; row < body.size(); row++) {
			Map rowmap = null;
			try {
				rowmap = BeanUtil.convertBean(body.get(row));
				
				Map<String, String> resultMap = MapSortUtil.sortMapByKey(rowmap);	
				
				Iterator iter = resultMap.entrySet().iterator();
				// 遍历并填充每一个数据
				for (int col = 0; iter.hasNext(); col++) {
					Map.Entry entry = (Map.Entry) iter.next();
					String key = entry.getKey().toString();
					String val = entry.getValue().toString();
					// 填充数据
					cteateCell(row + 1, col, val);
				}
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IntrospectionException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}

	/**
	 * 创建内容单元格
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param row
	 *            HSSFRow
	 * @param col
	 *            short型的列索引
	 * @param align
	 *            对齐方式
	 * @param val
	 *            列值
	 */

    public void cteateCell(int rowNum, int colNum, String val) {
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null)
            row = sheet.createRow(rowNum);

        XSSFCell cell = row.createCell(colNum);
        cell.setCellType(HSSFCell.ENCODING_UTF_16);
//		cell.setCellValue(new HSSFRichTextString(val));
        cell.setCellValue(val);

        // 设置对齐方式,默认是左对齐
        // cellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell.setCellStyle(cellstyle);
    }

    //创建表头
	public void cteateHead() {
		for(int i=0; i < head.length; i++){
			cteateCell(0, i, head[i]);
		}
	}
	
	public void setColWidth(){
		for(int i=0; i < head.length; i++){
			//sheet.autoSizeColumn((short)i);
			sheet.setColumnWidth(i, (16*256));
			 
		}
	}
	/**
	 * 设置单元格格式和字体格式
	 * 
	 */

	public void setHeadStyle(HSSFCell cell) {
		/*
		 * 设置格式
		 */
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行

		// 设置单元格字体
		XSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 250);
		cellStyle.setFont(font);

		// 设置单元格背景色
		cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// 加载样式
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 输入EXCEL文件
	 * 
	 * @param fileName
	 *            文件名
	 */
	public Workbook outputExcel() {
		 return wb;
	}

	public List getBody() {
		return body;
	}

	public void setBody(List body) {
		this.body = body;
	}
}
